package untiring07_HelloJDBC;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import oracle.jdbc.replay.internal.ReplayableConnection;

public class EX01_SungJukDAO implements EX01_ISungJukDAO {

	private String DRV = "oracle.jdbc.driver.OracleDriver";
	private String URL = "jdbc:oracle:thin:untiring07/untiring07@112.216.253.125:1521:xe";
	private String insertSQL = "insert into EX01SungJuk values(exam.nextval,?,?,?,?,?,?,?)";
	private String selectSQL = " select*from EX01SungJuk";
	
	@Override
	public boolean insertSungJuk(EX01_SungJukVO s) {
		Connection conn = null;
		PreparedStatement pstmt = null; 
		boolean result = false;

		try {
			conn = this.openDatabase();
			pstmt = conn.prepareStatement(insertSQL);
			pstmt.setString(1, s.getName());
			pstmt.setInt(2, s.getEng());
			pstmt.setInt(3, s.getKor());
			pstmt.setInt(4, s.getMat());
			pstmt.setInt(5, s.getTot());
			pstmt.setFloat(6, s.getAvg());
			pstmt.setString(7, s.getGra());
			pstmt.executeUpdate();
			result = true;

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			this.closeDatabase(conn, pstmt, null);
		}

		return result;
	}
	  private Connection openDatabase() {
	      try {
	         Class.forName(DRV);
	         return DriverManager.getConnection(URL);
	      } catch (Exception e) {
	         e.printStackTrace();
	      }
	      return null;
	   }
	   
	
	   private void closeDatabase(Connection c, PreparedStatement p, ResultSet r) {
	      if (r != null) try{r.close(); r = null;} catch(Exception e){}
	      if (p != null) try{p.close(); p = null;} catch(Exception e){}
	      if (c != null) try{c.close(); c = null;} catch(Exception e){}
	   }
	@Override
	public List<EX01_SungJukVO> selectSungJuk() {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;


List<EX01_SungJukVO> result = new ArrayList<>();
		try{
		conn = this.openDatabase();
		pstmt = conn.prepareStatement(selectSQL);
		rs = pstmt.executeQuery();
		while(rs.next()){
			EX01_SungJukVO sj = new EX01_SungJukVO();
			sj.setSjno(rs.getInt("sjno"));
			sj.setName(rs.getString("name"));
			sj.setEng(rs.getInt("eng"));
			sj.setKor(rs.getInt("Kor"));
			sj.setMat(rs.getInt("Mat"));
			sj.setTot(rs.getInt("Tot"));
			sj.setAvg(rs.getFloat("Avg"));
			sj.setGra(rs.getString("Gra"));
			result.add(sj);
		}
		
	}catch(Exception ex){
		ex.printStackTrace();
	} finally {
		this.closeDatabase(conn, pstmt, null);
	}
	return result;
	}
	@Override
	public boolean updateSungJuk(EX01_SungJukVO s) {
		// TODO Auto-generated method stub
		return false;
	}

}